"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Oracle数据库连接及相关操作
注意事项：
1.连接数据库后务必要手动关闭
2.除函数、存储过程外，其他sql语句都只能单行执行，即不能有；分隔
"""
import cx_Oracle as Cx

from .Logger import Logger


class ComOracle:
    def __init__(self, host, port, user, password, service):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.service = service
        self.db = None
        self.log = Logger()

    def connect(self):
        """
        连接数据库
        """
        self.db = Cx.connect(self.user, self.password,
                             f'{self.host}:{self.port}/{self.service}',
                             encoding="UTF-8")

    def connect_with_sys(self):
        """
        以系统管理员连接数据库
        """
        self.db = Cx.connect('sys', self.password,
                             f'{self.host}:{self.port}/{self.service}',
                             mode=Cx.SYSDBA,
                             encoding="UTF-8")

    def exec_non_query(self, sql, args=None):
        """
        执行非查询类sql语句，只能单行执行，即不能有；分隔
        :param sql: 非查询类sql语句
        :param args: 查询参数
        :return: 无
        """
        self.log.info(sql)
        with self.db.cursor() as cur:
            if args:
                cur.execute(sql, args)
            else:
                cur.execute(sql)
            self.db.commit()

    def exec_query(self, sql, args=None):
        """
        执行查询类sql语句，只能单行执行，即不能有；分隔
        :param sql: 查询类sql语句
        :param args: 查询参数
        :return: 查询结果
        """
        self.log.info(sql)
        with self.db.cursor() as cur:
            if args:
                cur.execute(sql, args)
            else:
                cur.execute(sql)
            values = cur.fetchall()
            self.db.commit()
        return values

    def exec_many_sql(self, sql, *args):
        """
        执行多行插入sql语句
        :param sql: 插入sql语句
        :param args: 多行插入值
        :return: 无
        """
        self.log.info(sql, args)
        with self.db.cursor() as cur:
            cur.executemany(sql, list(args))
            self.db.commit()

    def exec_proc(self, proc_name, args=None):
        """
        调用存储过程
        :param proc_name: 存储过程名
        :param args: [入参1，入参2，出参1， 出参2……]
        :return: 返回值
        """
        with self.db.cursor() as cur:
            if args:
                res = cur.callproc(proc_name, args)
            else:
                res = cur.callproc(proc_name)
        return res

    def exec_func(self, func_name, res_type, args=None):
        """
        调用函数
        :param func_name: 函数名
        :param res_type: 返回值类型
        :param args: [入参1，入参2……]
        :return:
        """
        with self.db.cursor() as cur:
            if args:
                res = cur.callfunc(func_name, res_type, args)
            else:
                res = cur.callfunc(func_name, res_type)
        return res

    def close(self):
        """
        关闭数据库连接
        """
        if self.db:
            self.db.close()
